package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Saede;
import ModelLayer.Salg;
import ModelLayer.SalgSaede;

public class DBSalgSaede {
	/**
	 * Initializes instance variables
	 */
	private Connection con;

	/** Creates connection to the database and creates an instance of DBSalgSaede*/
	public DBSalgSaede()
	{
		con = DbConnection1.getInstance().getDBcon();
	}

	/** getAllSalgSaede finds all SalgSaede with
	  * @param retrieveAssociation (tells weather or not the returned SalgSaede
	  *  needs to be associated with a Salg and a Saede)
	  * @return
	  */
	public ArrayList<SalgSaede> getAllSalgSaede(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);	
	}
	
	/** searchSalgSaede with
	 * 
	 * @param attValue
	 * @param retriveAssociation (tells weather or not the returned SalgSaede
	  *  needs to be associated with a Salg and a Saede)
	 * @return
	 */
	public SalgSaede searchSalgSaede(int attValue, boolean retriveAssociation)
	{
		String wClause = "salgId = '" + attValue + "'";
		//System.out.println("SearchSalg " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	
	/** insertSalgSaede creates a SalgSaede SQL query to be inserted into database with
	 * 
	 * @param saede
	 * @param salg
	 * @return
	 * @throws Exception (If not inserted correctly)
	 */
	public int insertSalgSaede(Saede saede, Salg salg) throws Exception
	{ 
		int rc = -1;
		String query="INSERT INTO SalgSaede(SalgId, saedeId)  VALUES('"+
				
						salg.getSalgId() + "','"  +
						saede.getSaedeId()   +  "')";

		//System.out.println("insert : " + query);
		try
		{ // insert new salgSaede
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex)
		{
			//System.out.println("SalgSaede ikke oprettet");
			throw new Exception ("SalgSaede is not inserted correct");
		}
		return(rc);
	}
	
	/** deleteForestilling removes a SalgSaede from database with
	 * 
	 * @param salgId
	 * @return
	 */
	public int delete(int salgId)
	{
		int rc=-1;

		String query="DELETE FROM SalgSaede WHERE SalgId = '" +
				salgId + "'";
		//System.out.println(query);
		try{ // delete from person
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			//System.out.println("Delete exception in SalgSaede db: "+ex);
		}
		return(rc);
	}
	
	/** singleWhere is used when we only want to select one SalgSaede
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned SalgSaede
	 *  needs to be associated with a Salg and a Saede)
	 * @return
	 */
	private SalgSaede singleWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		SalgSaede salgSaede = new SalgSaede();	

		String query =  buildQuery(wClause);
		//System.out.println(query);
		try
		{ // read the saede from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				salgSaede = buildSalgSaede(results);
				
				stmt.close();
				if(retrieveAssociation)
				{   
					//association to Sal
					DBSalg dbSalg = new DBSalg();
					Salg salg = dbSalg.getSalg(salgSaede.getSalg().getSalgId(), false);
					//System.out.println("Salg is seleceted ");
					salgSaede.setSalg(salg);
					
					DBSaede dbSaede = new DBSaede();
					Saede saede = dbSaede.getSaede(salgSaede.getSaede().getSaedeId(), false);
					//System.out.println("Saede is seleceted ");
					salgSaede.setSaede(saede);
				}			
			}
			else{ //if no saede found
				salgSaede = null;
			}
		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception: "+e);
		}
		return salgSaede;
	}

	/** mischWere is used whenever we want to select more than one salgSaede or all salgSaeder
	 * 
	 * @param wClause
	 * @param retrieveAssociation (tells weather or not the returned salgSaeder needs to be associated with a Salg and a Saede)
	 * @return
	 */
	private ArrayList<SalgSaede> miscWhere(String wClause, boolean retrieveAssociation) 
	{
		ResultSet results;
		ArrayList<SalgSaede> list = new ArrayList<SalgSaede>();	
		String query =  buildQuery(wClause);

		try
		{ // read the salgsaede from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while(results.next())
			{
				SalgSaede SaedeObj = new SalgSaede();
				SaedeObj = buildSalgSaede(results);	
				list.add(SaedeObj);	
			}//end while
			stmt.close();     
			if(retrieveAssociation)
			{   
				for(SalgSaede salgSaede : list)
				{
					DBSalg dbSalg = new DBSalg();
					Salg salg = dbSalg.getSalg(salgSaede.getSalg().getSalgId(), false);
					//System.out.println("Salg is seleceted ");
					salgSaede.setSalg(salg);
					
					DBSaede dbSaede = new DBSaede();
					Saede saede = dbSaede.getSaede(salgSaede.getSaede().getSaedeId(), false);
					//System.out.println("Saede is seleceted ");
					salgSaede.setSaede(saede);
				}
			}//end if   

		}//end try	
		catch(Exception e)
		{
			//System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * buildQuery uses wClause insert String list of the attributes in a SalgSaede and builds the
	 * WHERE part of the query that is to be committed to the SQl database
	 * @param wClause 
	 * @return
	 */
	private String buildQuery(String wClause) 
	{
		String query="SELECT * FROM SalgSaede";

		if (wClause.length()>0)
		{
			query=query+" WHERE "+ wClause;
		}
		return query;
	}

	/** buildSalgSaede creates a salgSaede object with
	 * 
	 * @param results (used to bring attributes to salgSaedeObj)
	 * @return
	 */
	private SalgSaede buildSalgSaede(ResultSet results) 
	{
		SalgSaede salgSaedeObj = new SalgSaede();
		try
		{ // the columns from the table person  are used
			salgSaedeObj.setSalg(new Salg(results.getInt("SalgId")));
			salgSaedeObj.setSaede(new Saede(results.getInt("SaedeId")));
		}
		catch(Exception e)
		{
			//System.out.println("error in building the salgSaede object");
		}
		return salgSaedeObj;
	}

}
